import sqlite3
import re
import requests
import time

# Test sql
conn = sqlite3.connect('./data/test.db')
conn.isolation_level = None  # 自动提交修改，不用手动commit
cur = conn.cursor()  # 创建游标
cur.execute('CREATE TABLE Video (id INTEGER PRIMARY KEY, aid VARCHAR(10), title VARCHAR(50), view INTEGER, '
            'danmaku INTEGER, reply INTEGER, like INTEGER, coin INTEGER, favorite INTEGER, share INTEGER, '
            'maintype VARCHAR(10), tag VARCHAR(50), hint INTEGER)')

# Bilibili APIs
rid = {1: '动画', 3: '音乐', 129: '舞蹈', 4: '游戏', 36: '知识', 188: '科技', 234: '运动', 223:'汽车', 160: '生活',
           211: '美食', 217: '动物', 119: '鬼畜', 155: '时尚', 5: '娱乐', 181: '影视'}
url0 = "https://api.bilibili.com/x/web-interface/dynamic/region?ps=20&rid="
url1 = "http://api.bilibili.com/x/tag/archive/tags?aid="
pos, tid = 0, 1
url = url0 + str(tid)
req = requests.get(url)
content = req.text
titles = re.findall(r'"title":"([^\"]*)"', content)
stats = re.findall(r'"stat":{(.*?)}', content)
descs = re.findall(r'"desc":"([^\"]*)",', content)
tnames = re.findall(r'"tname":"(.*?)"', content)
for i in range(20):
    # 处理视频的用户评价数据
    data = re.findall(r'"[a-z]*":(\d*)', stats[i])
    # 处理视频标签数据
    tag_req = requests.get(url1 + data[0])
    time.sleep(0.8)
    tags = re.findall(r'"tag_name":"(.*?)"', tag_req.text)
    tags.append(rid[tid])
    tags.append(tnames[i])
    # 处理视频的引导数据
    hint = 1 if re.findall(r'点赞|投币|收藏|关注|三连|3连', descs[i]) else 0
    # 将视频信息写入文件
    inp = '(' + str(pos) + ', \'' + data[0] + '\', \'' + titles[i].replace("'", "''") + '\', ' + data[1] + ', ' + data[2] + ', ' + data[3] + ',' \
          + data[-2] + ',' + data[5] + ',' + data[4] + ',' + data[6] + ', \'' + rid[tid] + '\', \'' \
          + str(tags).replace('\'', '"') + '\', ' + str(hint) + ')'
    print(inp)
    cur.execute("INSERT INTO Video VALUES" + inp)
    pos += 1
print("Successfully processed section " + str(tid))

cur.execute("SELECT * FROM Video")
print(cur.fetchall())
cur.close()
conn.close()


